/**
 * TabVD is a Virtual Desktop Client/Server designed specifically for connecting
 * from Tablet like devices.
 *
 * Copyright (C) 2011 Subhabrata Ghosh
 *
 * This program is free software: you can redistribute it and/or modify it under
 * the terms of the GNU General Public License as published by the Free Software
 * Foundation, either version 3 of the License, or (at your option) any later
 * version.
 *
 * This program is distributed in the hope that it will be useful, but WITHOUT
 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
 * FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
 * details.
 *
 * You should have received a copy of the GNU General Public License along with
 * this program. If not, see <http://www.gnu.org/licenses/>.
 *
 * DBHelper.java
 *
 *  Created on: Jul 19, 2011
 *      Author: subhagho
 */
package net.langene.tabvd.core.db;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.util.Date;
import java.util.List;

import org.w3c.dom.Element;
import org.w3c.dom.NodeList;

import net.langene.tabvd.core.XMLHelper;
import net.langene.tabvd.core.db.Entities.EntityBase;
import net.langene.tabvd.core.db.Schema.Column;
import net.langene.tabvd.core.db.Schema.DataType;
import net.langene.tabvd.core.db.Schema.Table;

/**
 * @author subhagho
 * 
 */
public class DBHelper {
	public static final String _XPATH_DB_TABLES_ = "/tabvd/setup/db/tables/table";
	public static final String _XPATH_DB_DATA_ = "/tabvd/setup/db/data/tables/table";

	/**
	 * Convert java.util.Date to java.sql.Date
	 * 
	 * net.langene.tabvd.core.db.DBHelper
	 * 
	 * @param date
	 * @return
	 */
	public static java.sql.Date toSqlDate(Date date) {
		return new java.sql.Date(date.getTime());
	}

	/**
	 * Convert java.util.Date to java.sql.Timestamp
	 * 
	 * net.langene.tabvd.core.db.DBHelper
	 * 
	 * @param date
	 * @return
	 */
	public static java.sql.Timestamp toSqlTimestamp(Date date) {
		return new java.sql.Timestamp(date.getTime());
	}

	/**
	 * Convert java.sql.Date to java.util.Date
	 * 
	 * net.langene.tabvd.core.db.DBHelper
	 * 
	 * @param date
	 * @return
	 */
	public static Date toDate(java.sql.Date date) {
		return new Date(date.getTime());
	}

	/**
	 * Convert java.sql.Timestamp to java.util.Date
	 * 
	 * net.langene.tabvd.core.db.DBHelper
	 * 
	 * @param timestamp
	 * @return
	 */
	public static Date timestampToDate(java.sql.Timestamp timestamp) {
		return new Date(timestamp.getTime());
	}

	public static Schema loadDbSchema(Connection conn) throws Exception {
		Schema schema = new Schema();
		DatabaseMetaData dbm = conn.getMetaData();
		ResultSet rs = dbm
				.getTables(null, null, null, new String[] { "TABLE" });
		while (rs.next()) {
			Table table = new Table();
			table.Name = rs.getString("TABLE_NAME");
			loadDbColumns(table, dbm);
			schema.Tables.put(table.Name, table);
		}
		return schema;
	}

	private static void loadDbColumns(Table table, DatabaseMetaData dbm)
			throws Exception {
		ResultSet rs = dbm.getColumns(null, null, table.Name, null);
		while (rs.next()) {
			Column column = new Column();
			column.Name = rs.getString("COLUMN_NAME");
			column.Type = Column.getDataType(rs.getInt("DATA_TYPE"));
			column.Size = rs.getInt("COLUMN_SIZE");
			table.Columns.add(column);
		}
		rs.close();
	}

	/**
	 * Load the Setup Data from the XML file.
	 * 
	 * net.langene.tabvd.core.db.DBHelper
	 * 
	 * @param helper
	 * @return
	 */
	public static List<EntityBase> loadXmlData(XMLHelper helper)
			throws Exception {
		return Entities.loadEntities(helper, _XPATH_DB_DATA_);
	}

	/**
	 * Load the Database Schema from the XML definition.
	 * 
	 * net.langene.tabvd.core.db.DBHelper
	 * 
	 * @param helper
	 * @return
	 * @throws Exception
	 */
	public static Schema loadSchema(XMLHelper helper) throws Exception {
		NodeList nodes = helper.find(_XPATH_DB_TABLES_);
		if (nodes == null || nodes.getLength() <= 0)
			throw new Exception("No table definitions found in file.");

		Schema schema = new Schema();
		for (int ii = 0; ii < nodes.getLength(); ii++) {
			Element nelem = (Element) nodes.item(ii);
			Table table = new Table();
			table.Name = nelem.getAttribute("name").toUpperCase();

			NodeList cnodes = helper.find(nelem, "./column");
			if (cnodes == null || cnodes.getLength() <= 0)
				throw new Exception(
						"Invalid table definition, no columns found for table. ["
								+ table.Name + "]");
			for (int jj = 0; jj < cnodes.getLength(); jj++) {
				Element celem = (Element) cnodes.item(jj);
				Column col = new Column();
				col.Name = celem.getAttribute("name").toUpperCase();
				col.Type = DataType.valueOf(celem.getAttribute("type"));
				String value = celem.getAttribute("size");
				if (value != null && !value.isEmpty()) {
					col.Size = Integer.parseInt(value);
				}
				value = celem.getAttribute("nullable");
				if (value != null && !value.isEmpty()) {
					col.IsNullable = Boolean.valueOf(value);
				}
				value = celem.getAttribute("primarykey");
				if (value != null && !value.isEmpty()) {
					col.IsPrimaryKey = Boolean.valueOf(value);
				}
				col.validate();

				table.Columns.add(col);

				if (col.IsPrimaryKey) {
					table.addKeyColumn(col);
				}
			}
			schema.Tables.put(table.Name, table);
		}
		return schema;
	}
}
